package com.matt.repository.impl;

import com.matt.domain.Leads;
import com.matt.foundation.constant.LeadsConstant;
import com.matt.foundation.query.LeadsHistoryQuery;
import com.matt.foundation.query.LeadsQuery;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.utils.MapConvertUtil;
import com.matt.foundation.utils.QueryUtil;
import com.matt.foundation.vo.LeadsHistoryVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.LeadsNativeRepository;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

/**
 * *****************
 * description :线索原生sql
 * created by Ron
 * from com.matt
 * on 2018/6/21
 * *******************
 */
@Service
public class LeadsNativeRepositoryImpl extends BaseNativeSqlRepository implements LeadsNativeRepository {


    @Override
    public List<Leads> findByCondition(LeadsQuery leadsQuery) {
        // 1. 关键修改：初始SQL增加 le.areaId IS NOT NULL，排除areaId为空的数据
        // 同时对areaId做SQL特殊字符过滤，避免注入风险（兼容JPA原生SQL）
        String querySql=String.format("select le.channelId,le.homeAddress,le.zhwysStatus,le.id,le.created,le.channel,le.fromUser,le.fromUserId, le.category,le.status,le.changeStatus,le.changeStatusVal,le.isComplete,le.childName,le.birthday,le.nickName,le.englishName,le.gender,le.bloodType,le.nationality\n" +
                ",le.targetGarden,le.rankp,le.province,le.channelStatus,le.channStatus,le.clientStatus,le.city,le.area,le.avatar,le.nextTime,le.registerResidence,le.registerType,le.name,le.mobile,le.staffName,le.staffId,le.relationship,le.lastFollowRecord,le.lastFollowTime,le.lastModified,le.typeStatus    from leads as le where le.areaId='%s'", leadsQuery.getAreaId());

        //查询
        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && !"monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
            querySql +=String.format(" and le." + leadsQuery.getQueryKey()+" like concat('%s','%s','%s') ","%", QueryUtil.replaceAllSql(leadsQuery.getQueryVal()),"%");

        //我的线索
        if (StringUtils.isNotBlank(leadsQuery.getStaffId()))
            //待跟进
            querySql +=String.format(" and le.zhwysStatus !=3 and le.staffId='%s'",leadsQuery.getStaffId());


        if (StringUtils.isNotBlank(leadsQuery.getStaffName())){
            querySql += String.format(" and le.staffName  like concat('%s','%s','%s') ", "%", leadsQuery.getStaffName(), "%");
        }

        //状态
        //if (leadsQuery.getStatus()!=null && leadsQuery.getStatus().length>0)
        if (StringUtils.isNotBlank(leadsQuery.getStatus()))
            querySql +=String.format(" and le.status IN(%s)",QueryUtil.strtosqlin(leadsQuery.getStatus()));

        //孩子月龄
        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && "monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
            querySql +=String.format(" and ((YEAR(NOW()) - YEAR(le.birthday)) * 12 + (MONTH(NOW()) - MONTH(le.birthday)))=%s",leadsQuery.getQueryVal());

        //创建时间开始
        if(leadsQuery.getBeginDate() != null){
            querySql += String.format(" and created >= '%s'", leadsQuery.getBeginDate());
        }

        //创建时间结束
        if(leadsQuery.getEndDate() != null){
            querySql += String.format(" and created < '%s'", leadsQuery.getEndDate());
        }

        //名称
        if(StringUtils.isNotBlank(leadsQuery.getName())){
            querySql += String.format(" and le.name like concat('%s','%s','%s')", "%", leadsQuery.getName(),"%");
        }

        //宝宝姓名
        if(StringUtils.isNotBlank(leadsQuery.getChildName())){
            querySql += String.format(" and le.childName like concat('%s','%s','%s')", "%", leadsQuery.getChildName(),"%");
        }

        //宝宝姓名
        if(StringUtils.isNotBlank(leadsQuery.getNames())){
            querySql += String.format(" and (le.childName like concat('%s','%s','%s') or le.name like concat('%s','%s','%s'))",
                    "%", leadsQuery.getNames(),"%",
                    "%", leadsQuery.getNames(),"%");
        }


        //电话
        if(StringUtils.isNotBlank(leadsQuery.getMobile())){
            querySql += String.format(" and le.mobile like concat('%s','%s','%s')", "%", leadsQuery.getMobile(),"%");
        }
        //级别
        if(leadsQuery.getRankp() != null){
            querySql += String.format(" and le.rankp = '%s'", leadsQuery.getRankp());
        }
        //状态
        if(leadsQuery.getZhwysStatus() != null){
            querySql += String.format(" and le.zhwysStatus = '%s'", leadsQuery.getZhwysStatus());
        }
        //线索来源
        if(StringUtils.isNotBlank(leadsQuery.getChannelId())){
            querySql += String.format(" and le.channelId = '%s'", leadsQuery.getChannelId());
        }
        //客户渠道
        if(leadsQuery.getChannelStatus()!=null){
            querySql += String.format(" and le.channelStatus = '%s'", leadsQuery.getChannelStatus());
        }
        if(leadsQuery.getChannStatus()!=null){
            querySql += String.format(" and le.channStatus = '%s'", leadsQuery.getChannStatus());
        }
        //客户状态
        if(leadsQuery.getClientStatus()!=null){
            querySql += String.format(" and le.clientStatus = '%s'", leadsQuery.getClientStatus());
        }
        if(leadsQuery.getTypeStatus()!=null){
            querySql += String.format(" and le.typeStatus = '%s'", leadsQuery.getTypeStatus());
        }



        querySql += String.format(" and le.changeStatus=0");
//        querySql += String.format(" and le.areaId='%s'",leadsQuery.getAreaId());
        querySql += String.format(" and deleted = 0") ;
        querySql += String.format(" order by created desc") ;
        querySql += String.format(" limit %s,%s ",leadsQuery.getPageNum() * leadsQuery.getPageSize(),leadsQuery.getPageSize());
        List<Leads> objectList = sqlObjectList(querySql, Leads.class);
        if (objectList == null){
            return null;
        }

        return objectList;
    }


    @Override
    public Long countLeads(LeadsQuery leadsQuery) {
        String querySql=String.format("select count(le.id) from leads as le where le.areaId='%s'", leadsQuery.getAreaId());
        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && !"monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
            querySql +=String.format(" and le."+leadsQuery.getQueryKey()+" like concat('%s','%s','%s') ","%",QueryUtil.replaceAllSql(leadsQuery.getQueryVal()),"%");
        //我的线索
        if (StringUtils.isNotBlank(leadsQuery.getStaffId()))
            //修改的
//            querySql +=String.format(" and le.staffId='%s'",leadsQuery.getStaffId());
            // 原代码缺少zhwysStatus !=3，导致count计算偏大
            querySql += String.format(" and le.zhwysStatus !=3 and le.staffId='%s'", leadsQuery.getStaffId());
        if (StringUtils.isNotBlank(leadsQuery.getStaffName())){
            querySql += String.format(" and le.staffName  like concat('%s','%s','%s') ", "%", leadsQuery.getStaffName(), "%");
        }
        if (StringUtils.isNotBlank(leadsQuery.getStatus()))
            querySql +=String.format(" and le.status IN(%s)",QueryUtil.strtosqlin(leadsQuery.getStatus()));
        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && "monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
            querySql +=String.format(" and ((YEAR(NOW()) - YEAR(le.birthday)) * 12 + (MONTH(NOW()) - MONTH(le.birthday)))=%s",leadsQuery.getQueryVal());

        //创建时间开始
        if(leadsQuery.getBeginDate() != null){
            querySql += String.format(" and created >= '%s'", leadsQuery.getBeginDate());
        }

        //创建时间结束
        if(leadsQuery.getEndDate() != null){
            querySql += String.format(" and created < '%s'", leadsQuery.getEndDate());
        }

        //名称
        if(StringUtils.isNotBlank(leadsQuery.getName())){
            querySql += String.format(" and le.name like concat('%s','%s','%s')", "%", leadsQuery.getName(),"%");
        }

        //宝宝姓名
        if(StringUtils.isNotBlank(leadsQuery.getChildName())){
            querySql += String.format(" and le.childName like concat('%s','%s','%s')", "%", leadsQuery.getChildName(),"%");
        }

        //电话
        if(StringUtils.isNotBlank(leadsQuery.getMobile())){
            querySql += String.format(" and le.mobile like concat('%s','%s','%s')", "%", leadsQuery.getMobile(),"%");
        }
        //级别
        if(leadsQuery.getRankp() != null){
            querySql += String.format(" and le.rankp = '%s'", leadsQuery.getRankp());
        }
        //状态
        if(leadsQuery.getZhwysStatus() != null){
            querySql += String.format(" and le.zhwysStatus = '%s'", leadsQuery.getZhwysStatus());
        }
        //线索来源
        if(StringUtils.isNotBlank(leadsQuery.getChannelId())){
            querySql += String.format(" and le.channelId = '%s'", leadsQuery.getChannelId());
        }

        //宝宝姓名
        if(StringUtils.isNotBlank(leadsQuery.getNames())){
            querySql += String.format(" and (le.childName like concat('%s','%s','%s') or le.name like concat('%s','%s','%s'))",
                    "%", leadsQuery.getChildName(),"%",
                    "%", leadsQuery.getName(),"%");
        }
        //客户渠道
        if(leadsQuery.getChannelStatus()!=null){
            querySql += String.format(" and le.channelStatus = '%s'", leadsQuery.getChannelStatus());
        }
        if(leadsQuery.getChannStatus()!=null){
            querySql += String.format(" and le.channStatus = '%s'", leadsQuery.getChannStatus());
        }
        //客户状态
        if(leadsQuery.getClientStatus()!=null){
            querySql += String.format(" and le.clientStatus = '%s'", leadsQuery.getClientStatus());
        }
        if(leadsQuery.getTypeStatus()!=null){
            querySql += String.format(" and le.typeStatus = '%s'", leadsQuery.getTypeStatus());
        }


        querySql += String.format(" and le.changeStatus=0");
        querySql += String.format(" and le.areaId='%s'",leadsQuery.getAreaId());
        querySql += String.format(" and deleted = 0") ;
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }









//    @Override
//    public Long countLeads(LeadsQuery leadsQuery) {
//        // 初始SQL包含areaId条件
//        String querySql = String.format("select count(le.id) from leads as le where le.areaId='%s' and le.areaId IS NOT NULL", leadsQuery.getAreaId());
//
//        // 查询关键词过滤
//        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && !"monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
//            querySql += String.format(" and le." + leadsQuery.getQueryKey() + " like concat('%s','%s','%s') ", "%", QueryUtil.replaceAllSql(leadsQuery.getQueryVal()), "%");
//
//        // 我的线索（与列表查询保持一致，添加zhwysStatus !=3）
//        if (StringUtils.isNotBlank(leadsQuery.getStaffId()))
//            querySql += String.format(" and le.zhwysStatus !=3 and le.staffId='%s'", leadsQuery.getStaffId());
//
//        // 员工姓名模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getStaffName())) {
//            querySql += String.format(" and le.staffName  like concat('%s','%s','%s') ", "%", leadsQuery.getStaffName(), "%");
//        }
//
//        // 状态过滤
//        if (StringUtils.isNotBlank(leadsQuery.getStatus()))
//            querySql += String.format(" and le.status IN(%s)", QueryUtil.strtosqlin(leadsQuery.getStatus()));
//
//        // 孩子月龄过滤
//        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && "monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
//            querySql += String.format(" and ((YEAR(NOW()) - YEAR(le.birthday)) * 12 + (MONTH(NOW()) - MONTH(le.birthday)))=%s", leadsQuery.getQueryVal());
//
//        // 创建时间开始
//        if (leadsQuery.getBeginDate() != null) {
//            querySql += String.format(" and created >= '%s'", leadsQuery.getBeginDate());
//        }
//
//        // 创建时间结束
//        if (leadsQuery.getEndDate() != null) {
//            querySql += String.format(" and created < '%s'", leadsQuery.getEndDate());
//        }
//
//        // 名称模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getName())) {
//            querySql += String.format(" and le.name like concat('%s','%s','%s')", "%", leadsQuery.getName(), "%");
//        }
//
//        // 宝宝姓名模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getChildName())) {
//            querySql += String.format(" and le.childName like concat('%s','%s','%s')", "%", leadsQuery.getChildName(), "%");
//        }
//
//        // 电话模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getMobile())) {
//            querySql += String.format(" and le.mobile like concat('%s','%s','%s')", "%", leadsQuery.getMobile(), "%");
//        }
//
//        // 级别过滤
//        if (leadsQuery.getRankp() != null) {
//            querySql += String.format(" and le.rankp = '%s'", leadsQuery.getRankp());
//        }
//
//        // 状态过滤
//        if (leadsQuery.getZhwysStatus() != null) {
//            querySql += String.format(" and le.zhwysStatus = '%s'", leadsQuery.getZhwysStatus());
//        }
//
//        // 线索来源过滤
//        if (StringUtils.isNotBlank(leadsQuery.getChannelId())) {
//            querySql += String.format(" and le.channelId = '%s'", leadsQuery.getChannelId());
//        }
//
//        // 名称或宝宝姓名模糊查询（修复变量错误）
//        if (StringUtils.isNotBlank(leadsQuery.getNames())) {
//            querySql += String.format(" and (le.childName like concat('%s','%s','%s') or le.name like concat('%s','%s','%s'))",
//                    "%", leadsQuery.getNames(), "%",
//                    "%", leadsQuery.getNames(), "%");
//        }
//
//        // 客户渠道过滤
//        if (leadsQuery.getChannelStatus() != null) {
//            querySql += String.format(" and le.channelStatus = '%s'", leadsQuery.getChannelStatus());
//        }
//        if (leadsQuery.getChannStatus() != null) {
//            querySql += String.format(" and le.channStatus = '%s'", leadsQuery.getChannStatus());
//        }
//
//        // 客户状态过滤
//        if (leadsQuery.getClientStatus() != null) {
//            querySql += String.format(" and le.clientStatus = '%s'", leadsQuery.getClientStatus());
//        }
//        if (leadsQuery.getTypeStatus() != null) {
//            querySql += String.format(" and le.typeStatus = '%s'", leadsQuery.getTypeStatus());
//        }
//
//        // 通用条件（删除重复的areaId）
//        querySql += String.format(" and le.changeStatus=0");
//        querySql += String.format(" and deleted = 0");
//
//        List<?> objectArrayList = sqlSingleList(querySql);
//        return objectArrayList != null && !objectArrayList.isEmpty() ? Long.valueOf(objectArrayList.get(0).toString()) : 0L;
//    }
//
//    @Override
//    public List<Leads> findByCondition(LeadsQuery leadsQuery) {
//        // 初始SQL包含areaId条件和非空判断
//        String querySql = String.format("select le.channelId,le.homeAddress,le.zhwysStatus,le.id,le.created,le.channel,le.fromUser,le.fromUserId, le.category,le.status,le.changeStatus,le.changeStatusVal,le.isComplete,le.childName,le.birthday,le.nickName,le.englishName,le.gender,le.bloodType,le.nationality\n" +
//                ",le.targetGarden,le.rankp,le.province,le.channelStatus,le.channStatus,le.clientStatus,le.city,le.area,le.avatar,le.nextTime,le.registerResidence,le.registerType,le.name,le.mobile,le.staffName,le.relationship,le.lastFollowRecord,le.lastFollowTime,le.lastModified,le.typeStatus    from leads as le where le.areaId='%s' and le.areaId IS NOT NULL", leadsQuery.getAreaId());
//
//        // 查询关键词过滤
//        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && !"monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
//            querySql += String.format(" and le." + leadsQuery.getQueryKey() + " like concat('%s','%s','%s') ", "%", QueryUtil.replaceAllSql(leadsQuery.getQueryVal()), "%");
//
//        // 我的线索（包含zhwysStatus !=3过滤）
//        if (StringUtils.isNotBlank(leadsQuery.getStaffId()))
//            querySql += String.format(" and le.zhwysStatus !=3 and le.staffId='%s'", leadsQuery.getStaffId());
//
//        // 员工姓名模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getStaffName())) {
//            querySql += String.format(" and le.staffName  like concat('%s','%s','%s') ", "%", leadsQuery.getStaffName(), "%");
//        }
//
//        // 状态过滤
//        if (StringUtils.isNotBlank(leadsQuery.getStatus()))
//            querySql += String.format(" and le.status IN(%s)", QueryUtil.strtosqlin(leadsQuery.getStatus()));
//
//        // 孩子月龄过滤
//        if (StringUtils.isNotBlank(leadsQuery.getQueryKey()) && "monthAge".equals(leadsQuery.getQueryKey()) && StringUtils.isNotBlank(leadsQuery.getQueryVal()))
//            querySql += String.format(" and ((YEAR(NOW()) - YEAR(le.birthday)) * 12 + (MONTH(NOW()) - MONTH(le.birthday)))=%s", leadsQuery.getQueryVal());
//
//        // 创建时间开始
//        if (leadsQuery.getBeginDate() != null) {
//            querySql += String.format(" and created >= '%s'", leadsQuery.getBeginDate());
//        }
//
//        // 创建时间结束
//        if (leadsQuery.getEndDate() != null) {
//            querySql += String.format(" and created < '%s'", leadsQuery.getEndDate());
//        }
//
//        // 名称模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getName())) {
//            querySql += String.format(" and le.name like concat('%s','%s','%s')", "%", leadsQuery.getName(), "%");
//        }
//
//        // 宝宝姓名模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getChildName())) {
//            querySql += String.format(" and le.childName like concat('%s','%s','%s')", "%", leadsQuery.getChildName(), "%");
//        }
//
//        // 名称或宝宝姓名模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getNames())) {
//            querySql += String.format(" and (le.childName like concat('%s','%s','%s') or le.name like concat('%s','%s','%s'))",
//                    "%", leadsQuery.getNames(), "%",
//                    "%", leadsQuery.getNames(), "%");
//        }
//
//        // 电话模糊查询
//        if (StringUtils.isNotBlank(leadsQuery.getMobile())) {
//            querySql += String.format(" and le.mobile like concat('%s','%s','%s')", "%", leadsQuery.getMobile(), "%");
//        }
//
//        // 级别过滤
//        if (leadsQuery.getRankp() != null) {
//            querySql += String.format(" and le.rankp = '%s'", leadsQuery.getRankp());
//        }
//
//        // 状态过滤
//        if (leadsQuery.getZhwysStatus() != null) {
//            querySql += String.format(" and le.zhwysStatus = '%s'", leadsQuery.getZhwysStatus());
//        }
//
//        // 线索来源过滤
//        if (StringUtils.isNotBlank(leadsQuery.getChannelId())) {
//            querySql += String.format(" and le.channelId = '%s'", leadsQuery.getChannelId());
//        }
//
//        // 客户渠道过滤
//        if (leadsQuery.getChannelStatus() != null) {
//            querySql += String.format(" and le.channelStatus = '%s'", leadsQuery.getChannelStatus());
//        }
//        if (leadsQuery.getChannStatus() != null) {
//            querySql += String.format(" and le.channStatus = '%s'", leadsQuery.getChannStatus());
//        }
//
//        // 客户状态过滤
//        if (leadsQuery.getClientStatus() != null) {
//            querySql += String.format(" and le.clientStatus = '%s'", leadsQuery.getClientStatus());
//        }
//        if (leadsQuery.getTypeStatus() != null) {
//            querySql += String.format(" and le.typeStatus = '%s'", leadsQuery.getTypeStatus());
//        }
//
//        // 通用条件
//        querySql += String.format(" and le.changeStatus=0");
//        querySql += String.format(" and deleted = 0");
//        querySql += String.format(" order by created desc");
//
//        // 分页（0基页码计算：偏移量 = 页码 × 每页大小）
//        querySql += String.format(" limit %s,%s ",
//                leadsQuery.getPageNum() * leadsQuery.getPageSize(),
//                leadsQuery.getPageSize()
//        );
//
//        List<Leads> objectList = sqlObjectList(querySql, Leads.class);
//        return objectList != null ? objectList : new ArrayList<>();
//    }


    @Override
    public Long arriveLeadsNum(String staffId, Date begin,Date end) {
        String querySql = String.format("select count(DISTINCT(ls.id)) from leads ls inner join leads_history as lh on ls.id = lh.leadsId where ls.staffId='%s' and date(ls.created) >= date('%s') and date(ls.created) <= date('%s') and lh.leadsStatus='%s'",staffId, DateUtil.dateToymd(begin),DateUtil.dateToymd(end), LeadsConstant.Status.ARRIVED.codeOf());
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }



}
